﻿Imports System.Data.SqlClient

Public Class frmProspectsCasesEdit

    Public SNo As Integer

    Private Sub frmProspectsDetails_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand("Select * From ProspectCases Where SNo=" & SNo, cnn)
            Dim Reader As SqlDataReader

            cnn.Open()
            Reader = cmd.ExecuteReader
            While (Reader.Read)
                Me.txtProsNo.Text = Reader.Item("ProsNo")
                Me.txtStatus.Text = Reader.Item("Status")
                Me.txtProspID.Text = Reader.Item("ProspectID")
                Me.txtProspName.Text = Reader.Item("ProspectName")
                Me.txtInsurComp.Text = Reader.Item("InsuranceCompany")
                Me.txtInsurBroker.Text = Reader.Item("InsuranceBroker")
                Me.txtClassOfBusiness.Text = Reader.Item("ClassofBusiness")
                Me.txtTargetRate.Text = Reader.Item("TargetRate")
                Me.txtNotes.Text = Reader.Item("Notes")
                'Check expiry date
                If Reader.Item("ExpiryDate") Is DBNull.Value Then
                    Me.ChExpiryDate.Checked = False
                Else
                    Me.ChExpiryDate.Checked = True
                    Me.DTPExpiryDate.Value = CDate(Reader.Item("ExpiryDate"))
                End If
                Me.txtBusinessSize.Text = Reader.Item("BusinessSize")
                Me.txtAssignedTo.Text = Reader.Item("AssignedTo")
                Me.txtAssignedBy.Text = Reader.Item("AssignedBy")
                Me.txtAssignedOn.Text = Reader.Item("AssignedDate")

            End While
            cnn.Close()

            'Check status
            If Me.txtStatus.Text = "Active" Then
                Me.txtStatus.BackColor = Color.Yellow

            ElseIf Me.txtStatus.Text = "Won" Then
                Me.txtStatus.BackColor = Color.GreenYellow

            ElseIf Me.txtStatus.Text = "Lost" Then
                Me.txtStatus.BackColor = Color.Red

            ElseIf Me.txtStatus.Text = "Non Desired" Then
                Me.txtStatus.BackColor = Color.Silver
            End If

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub ChExpiryDate_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles ChExpiryDate.CheckedChanged
        If Me.ChExpiryDate.Checked = True Then
            Me.DTPExpiryDate.Enabled = True
            Me.DTPExpiryDate.Value = Today.Date

        Else
            Me.DTPExpiryDate.Enabled = False
            Me.DTPExpiryDate.Value = Today.Date
        End If
    End Sub

    Private Sub btnClose_Click_1(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub btnSave_Click(sender As System.Object, e As System.EventArgs) Handles btnSave.Click
        If txtBusinessSize.Text.Length = 0 Then
            Me.ErrProv.SetError(txtBusinessSize, "Please fill in all required fields")
            Me.txtBusinessSize.Focus()
        Else
            Try
                Me.Cursor = Cursors.WaitCursor

                Dim cmd As New SqlCommand()
                Dim Trans As SqlTransaction

                cnn.Open()
                Trans = cnn.BeginTransaction
                cmd.Connection = cnn
                cmd.Transaction = Trans

                cmd.CommandText = "Update ProspectCases Set InsuranceCompany=@InsuranceCompany,InsuranceBroker=@InsuranceBroker," & _
                                  "BusinessSize=@BusinessSize,ExpiryDate=@ExpiryDate,TargetRate=@TargetRate,Notes=@Notes Where SNo=" & SNo

                'Add values
                cmd.Parameters.AddWithValue("@InsuranceCompany", Me.txtInsurComp.Text.Trim)
                cmd.Parameters.AddWithValue("@InsuranceBroker", Me.txtInsurBroker.Text.Trim)
                cmd.Parameters.AddWithValue("@TargetRate", Me.txtTargetRate.Text)
                cmd.Parameters.AddWithValue("@Notes", Me.txtNotes.Text)

                'Check if there is an existing policy that will expire
                If Me.ChExpiryDate.Checked = True Then
                    cmd.Parameters.AddWithValue("@ExpiryDate", Me.DTPExpiryDate.Value.ToShortDateString & " 10:10:10 AM")
                Else
                    cmd.Parameters.AddWithValue("@ExpiryDate", DBNull.Value)
                End If
                cmd.Parameters.AddWithValue("@BusinessSize", Me.txtBusinessSize.Text.Trim)

                cmd.ExecuteNonQuery()

                Trans.Commit()
                cnn.Close()

                MsgBox("Saved Successfully")

                Me.Cursor = Cursors.Default

                Me.Close()
            Catch ex As Exception
                Me.Cursor = Cursors.Default
                If cnn.State = ConnectionState.Open Then
                    cnn.Close()
                End If
                MsgBox(ex.ToString)
            End Try
        End If
    End Sub
End Class